package com.friday.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.friday.dao.SignInDao;
import com.friday.po.EvectionApply;
import com.friday.po.SignIn;
import com.friday.util.DBUtil;

public class SignInDaoImpl implements SignInDao {
	private Connection conn = null;
	private Statement stmt = null;
	private PreparedStatement pStmt = null;
	private ResultSet rs = null;
	private DBUtil util = null;

	@Override
	public int insertSignIn(SignIn si) throws Exception {
		String sql = "insert into signin values(?, ?, ?, ?, ?, ?, ?)";
		int num = 0;
		
		util = DBUtil.newInstance();
		conn = util.getConnection();
		conn.setAutoCommit(false);
		
		try {
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, si.getId());
			pStmt.setString(2, si.getEmployee());
			pStmt.setDate(3, si.getDate());
			pStmt.setTime(4,si.getStartTime());
			pStmt.setTime(5, si.getEndTime());
			pStmt.setInt(6, si.getState());
			pStmt.setString(7, si.getDepartment());
			
			num = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		}finally{
			util.close(pStmt);
			util.close(conn);
		}
		
		return num;
	}

	@Override
	public int deleteSignInById(String id) throws Exception {
		String sql = " delete from signin where id = ?";
		int num = 0;
		

		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			
			pStmt.setString(1, id);
			
			num = pStmt.executeUpdate();
			conn.commit();
			
		} catch (Exception e) {
			conn.rollback();
			throw new Exception("SignInDao delete error!");
			
		}finally{
			util.close(pStmt);
			util.close(conn);
		}
		
		return num;
	}

	@Override
	public int updateSignIn(SignIn si) throws Exception {
		String sql = "update signin set employee = ? ,  date = ? , startTime = ? , endTime = ? ,state = ? , department = ? where id = ?";
		int num;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			
			pStmt = conn.prepareStatement(sql);
			
			pStmt = conn.prepareStatement(sql);
			
			pStmt.setString(1, si.getEmployee());
			pStmt.setDate(2, si.getDate());
			pStmt.setTime(3,si.getStartTime());
			pStmt.setTime(4, si.getEndTime());
			pStmt.setInt(5, si.getState());
			pStmt.setString(6, si.getDepartment());
			pStmt.setString(7, si.getId());
			
			num = pStmt.executeUpdate();
			conn.commit();
			
		} catch (Exception e) {
			conn.rollback();
			throw new Exception("SignInDao update error!");
			
		}finally{
			util.close(stmt);
			util.close(conn);
		}
		
		return num;
	}

	@Override
	public List<SignIn> querySignInByDepartment(String department)
			throws Exception {
		String sql = "select * from signin where depatrment = \'"+ department +"\'";
		List<SignIn> list = new ArrayList<SignIn>();
		
		util = DBUtil.newInstance();
		conn = util.getConnection();
		stmt = conn.createStatement();
		
		rs = stmt.executeQuery(sql);
		
		try {
			while(rs.next()){
				SignIn si = new SignIn();
				
				si.setEmployee(rs.getString("employee"));
				si.setDate(rs.getDate("date"));
				si.setStartTime(rs.getTime("startTime"));
				si.setEndTime(rs.getTime("endTime"));
				si.setState(rs.getInt("state"));
				si.setDepartment(rs.getString("department"));
				si.setId(rs.getString("id"));
				
				list.add(si);
				
			}
		} catch (Exception e) {
			throw new Exception("SignInDao queryall error!");
			
		}finally{
			util.close(rs);
			util.close(stmt);
			util.close(conn);
			
		}
		return list;
	}

	@Override
	public SignIn querySignInByEmployee(String employee) throws Exception {
		String sql = "select * from signin where employee = \'"+ employee +"\'";
		SignIn si = null;
		
		
		util = DBUtil.newInstance();
		conn = util.getConnection();
		stmt = conn.createStatement();
		
		rs = stmt.executeQuery(sql);
		
		try{
			while (rs.next()) {
				si = new SignIn();
				si.setEmployee(rs.getString("employee"));
				si.setDate(rs.getDate("date"));
				si.setStartTime(rs.getTime("startTime"));
				si.setEndTime(rs.getTime("endTime"));
				si.setState(rs.getInt("state"));
				si.setDepartment(rs.getString("department"));
				si.setId(rs.getString("id"));
			
			//si = new SignIn(id, employee, date, startTime, endTime, state, department)
			}
		}catch(Exception e){
			throw e;
			
		}finally{
			util.close(rs);
			util.close(stmt);
			util.close(conn);
			
		}
		return si;
	}

	@Override
	public int deleteSignInByDate(Date date) throws Exception {
		String sql = " delete from signin where date = ?";
		int num = 0;
		

		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			
			pStmt.setDate(1, date);
			
			num = pStmt.executeUpdate();
			conn.commit();
			
		} catch (Exception e) {
			conn.rollback();
			throw new Exception("SignInDao delete error!");
			
		}finally{
			util.close(pStmt);
			util.close(conn);
		}
		
		return num;
	}

	@Override
	public List<SignIn> querySignInBetweenDate(Date start, Date end)
			throws Exception {
		String sql = "select * from signin where date between ? and ? order by date";
		List<SignIn> list = new ArrayList<SignIn>();
		
		util = DBUtil.newInstance();
		conn = util.getConnection();
		pStmt = conn.prepareStatement(sql);
		
		pStmt.setDate(1, start);
		pStmt.setDate(2, end);
		
		rs = pStmt.executeQuery(sql);
		
		try {
			while(rs.next()){
				SignIn si = new SignIn();
				
				si.setEmployee(rs.getString("employee"));
				si.setDate(rs.getDate("date"));
				si.setStartTime(rs.getTime("startTime"));
				si.setEndTime(rs.getTime("endTime"));
				si.setState(rs.getInt("state"));
				si.setDepartment(rs.getString("department"));
				si.setId(rs.getString("id"));
				
				list.add(si);
				
			}
		} catch (Exception e) {
			throw new Exception("SignInDao queryall error!");
			
		}finally{
			util.close(rs);
			util.close(stmt);
			util.close(conn);
			
		}
		return list;
	}

	@Override
	public List<SignIn> querySignInAll() throws Exception {
		String sql = "select * from signin ";
		List<SignIn> list = new ArrayList<SignIn>();
		
		util = DBUtil.newInstance();
		conn = util.getConnection();
		stmt = conn.createStatement();
		
		rs = stmt.executeQuery(sql);
		
		try {
			while(rs.next()){
				SignIn si = new SignIn();
				
				si.setEmployee(rs.getString("employee"));
				si.setDate(rs.getDate("date"));
				si.setStartTime(rs.getTime("startTime"));
				si.setEndTime(rs.getTime("endTime"));
				si.setState(rs.getInt("state"));
				si.setDepartment(rs.getString("department"));
				si.setId(rs.getString("id"));
				
				list.add(si);
				
			}
		} catch (Exception e) {
			throw new Exception("SignInDao queryall error!");
			
		}finally{
			util.close(rs);
			util.close(stmt);
			util.close(conn);
			
		}
		return list;
	}

}
